package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
	"xorm.io/xorm"
)

func main() {

	var (
		userName  = "root"
		password  = "gg5858518"
		ipAddress = "127.0.0.1"
		port      = 3306
		dbName    = "ds1"
		charset   = "utf8mb4"
	)

	// 构建数据库连接信息
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddress, port, dbName, charset)

	engine, err := xorm.NewEngine("mysql", dataSourceName)
	if err != nil {
		fmt.Println(err)
	}

	//syncTable(engine)

	//insertData(engine)

	//insertMultiData(engine)

	//updateData(engine)

	//deleteData(engine)

	//executeSql(engine)

	//selectData(engine)

	transactionData(engine)
}

// 事务
func transactionData(engine *xorm.Engine) {
	session := engine.NewSession()
	defer session.Close()

	session.Begin()
	defer func() {
		if err := recover(); err != nil {
			fmt.Println(err)
			session.Rollback()
		} else {
			session.Commit()
		}
	}()

	user1 := TbUser{Id: 1006, Name: "sali123", Age: 18, Passwd: "11111111"}
	if _, err := session.Insert(&user1); err != nil {
		panic(err)
	}

	user2 := TbUser{Name: "sali-2", Age: 27}
	if _, err := session.Where("id=1000").Update(&user2); err != nil {
		panic(err)
	}

	if _, err := session.Exec("delete from tb_user where name = 'salisali3'"); err != nil {
		panic(err)
	}
}

// 查询
func selectData(engine *xorm.Engine) {
	user1 := TbUser{Name: "sali-2"}
	engine.Where("name = ?", user1.Name).Desc("id").Get(&user1)
	fmt.Println(user1)

	user := TbUser{}
	var name string
	engine.Table(&user).Where("id=1002").Cols("Name").Get(&name)
	fmt.Println(name)

	var users []TbUser
	engine.Where("passwd=123456").And("age=27").Limit(10, 0).Find(&users)
	fmt.Println(users)

	user = TbUser{Passwd: "123456"}
	counts, _ := engine.Count(&user)
	fmt.Println(counts)
}

// 自定义 sql
func executeSql(engine *xorm.Engine) {
	engine.Exec("update tb_user set age = ? where id = ?", 10, 1004)
}

// 删除数据
func deleteData(engine *xorm.Engine) {
	user := TbUser{Name: "salisali1"}
	n, _ := engine.ID(1001).Delete(&user)
	fmt.Println(n)
}

// 更新数据
func updateData(engine *xorm.Engine) {
	user := TbUser{Name: "sali-2"}
	n, _ := engine.ID(1000).Update(&user)
	fmt.Println(n)
}

// 插入数据
func insertData(engine *xorm.Engine) {
	user := TbUser{Id: 1000, Name: "salisali", Age: 27, Passwd: "123456"}
	n, _ := engine.Insert(&user)
	fmt.Println(n)
}

// 批量插入
func insertMultiData(engine *xorm.Engine) {
	user1 := TbUser{Id: 1001, Name: "salisali1", Age: 27, Passwd: "123456"}
	user2 := TbUser{Id: 1002, Name: "salisali2", Age: 27, Passwd: "123456"}
	n, _ := engine.Insert(&user1, &user2)
	fmt.Println(n)

	user3 := TbUser{Id: 1003, Name: "salisali3", Age: 27, Passwd: "123456"}
	user4 := TbUser{Id: 1004, Name: "salisali4", Age: 27, Passwd: "123456"}
	var users []TbUser
	users = append(users, user3, user4)
	n2, _ := engine.Insert(users)
	fmt.Println(n2)
}

// 同步一张表
func syncTable(engine *xorm.Engine) {
	err := engine.Sync(new(TbUser))
	if err != nil {
		fmt.Println(err)
	}
}

type TbUser struct {
	Id      int64
	Name    string
	Age     int
	Passwd  string    `xorm:"varchar(200)"`
	Created time.Time `xorm:"created"`
	Updated time.Time `xorm:"updated"`
}
